package db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>Title: 执行数据库操作的类</p>
 * <p>Description: </p>
 * @author zhangzuoqiang
 * <br/>Date: 2011-3-8
 */
public final class ExeSQL {
	
	/**
	 * 测试入口
	 * @param args
	 */
	public static void main(String[] args) {
		List<DataVO> list = ExeSQL.selectDataVOs(22);
		System.out.println(list.size());
	}

	protected static DBConnManager cm = DBConnManager.getInstance();
	private static Connection conn = null;
	private static Statement stmt = null;
	private static ResultSet rs = null;
	
	/**
	 * 删除一个用户
	 * @param id
	 * @return
	 */
	public static boolean deleteDataVO(int id){
		boolean flag = false;
		try {
			 conn = cm.getConnection();
			 stmt = conn.createStatement();
			 flag = stmt.execute(SQLangs.deleteDataVO(id));
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			closeAll();
		}
		return flag;
	}

	/**
	 * 执行添加用户
	 * @param vo
	 * @return
	 */
	public static boolean addDataVO(DataVO vo){
		boolean flag = false;
		try {
			 conn = cm.getConnection();
			 stmt = conn.createStatement();
			 flag = stmt.execute(SQLangs.addDataVO(vo));
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			closeAll();
		}
		return flag;
	}
	
	/**
	 * 根据age，查询用户列表
	 * @param age
	 * @return
	 */
	public static ArrayList<DataVO> selectDataVOs(int age){
		ArrayList<DataVO> list = new ArrayList<DataVO>();
		try {
			 conn = cm.getConnection();
			 stmt = conn.createStatement();
			 rs = stmt.executeQuery(SQLangs.selectDataVOsByAge(age));
			 while (rs.next()) {
				 DataVO data = new DataVO();
				 data.setId(rs.getInt(1));
				 data.setName(rs.getString(2));
				 data.setAge(rs.getInt(3));
				 data.setIsgraduate(rs.getInt(4));
				 list.add(data);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			closeAll();
		}
		return list;
	}
	
	/**
	 * 关闭相关连接
	 */
	protected static void closeAll(){
		if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
            	
            }
        }
        
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
            	
            }
        }
        
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
            	
            }
        }
	}
}
